from typing import Tuple
from pandas import DataFrame,concat
from openpyxl import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from pandas.core.series import Series
from numpy import ceil


from .predata import data_reader
from .exsheet import exblock_in_sheet, Exblock, Coord

class pattern_copier:

    NARROW = {
        'top':0.75, 'bottom':0.75,
        'left':0.25, 'right':0.25,
        'header':0.3, 'footer':0.3
    }
    NARROWER={
        'top':0.4, 'bottom':0.35,
        'left':0.25, 'right':0.25,
        'header':0.3, 'footer':0.3
    }
    WIDE = {
        'top':1, 'bottom':1,
        'left':1, 'right':1,
        'header':0.5, 'footer':0.5     
    }
    NORMAL = {
        'top':0.75, 'bottom':0.75,
        'left':0.7, 'right':0.7,
        'header':0.3, 'footer':0.3
    }

    def __init__(self)->None:
        self._tidy_data : DataFrame = None
        self._receipt : str = None
        self._filled_datas : list[Tuple] = None
        self._exb_in_st : exblock_in_sheet = None
        self._exblock : Exblock = None
        self._wb : Workbook = None
        pass
        # self.tidy_data : DataFrame = None
        # self.splited_data_by_block : Tuple(Series,DataFrame) = None
        # self.dreader : data_reader = None

    @property
    def tidy_data(self):
        return self._tidy_data

    @tidy_data.setter
    def tidy_data(self,value):
        self._tidy_data = value

    @property
    def oto_loc(self):
        return self._oto_loc

    @property
    def mtm_loc(self):
        return self._mtm_loc
        
    @property
    def filled_data(self):
        adata = self.tidy_data[self.oto_loc.values()].drop_duplicates()
        df = self.tidy_data
        filled_datas = []
        if self.mtm_loc is not None:
            rng = [loc for loc in self.mtm_loc if self.mtm_loc[loc]=='行范围'][0].split(':')
            start = int(rng[0])
            end = int(rng[1])
            number_of_rows = end - start + 1
            
            # print(df.columns)
            for _,row in adata.iterrows():
                
                # constract filter condition
                # 这里需要区分字段类型来确定是否加引号
                query = " & ".join([f"`{col}`=='{row[col]}'" for col in self.oto_loc.values()])
                # print(query)
                # print(df.eval(query))
                match = df.query(query)
                if match.shape[0]<=number_of_rows:
                    filled_datas.append((row,match))
                else:
                    for j in range(0, int(ceil(match.shape[0]/number_of_rows))):
                        filled_datas.append((row,match.iloc[j*number_of_rows:(j+1)*number_of_rows]))

        else:
            for _, row in adata.iterrows():
                filled_datas.append((row, DataFrame([])))
        
        self._filled_datas = filled_datas
        return self._filled_datas

    @property
    def exblock(self):
        return self._exblock

    def read_data(self, data_file, idx=0, factory_name:str=None, receipt:str='请款单'):

        self._receipt = receipt

        self.dreader = data_reader(data_file=data_file,idx=idx, factory_name=factory_name ,receipt=receipt)
        self._oto_loc = self.dreader.get_oto_loc(receipt)
        self._mtm_loc = self.dreader.mtm_loc
        self._tidy_data = self.dreader.tidy_data      

    def read_pattern(self, pattern_file, idx:int=0)->exblock:
        self._exb_in_st = exblock_in_sheet()
        self._exblock = self._exb_in_st.read_pattern(pattern_file,idx)
        return self._exblock

    def write_in_sheet(self,
        absolute_loc:Coord=Coord(1,1), 
        gap_between_blocks_in_a_page:int=0, 
        gap_between_pages:int=0, 
        number_of_blocks_per_page:int=1
    ):
        self._exb_in_st.absolute_loc = absolute_loc
        self._exb_in_st.gap_between_blocks_in_a_page = gap_between_blocks_in_a_page
        self._exb_in_st.gap_between_pages = gap_between_pages
        self._exb_in_st.number_of_blocks_per_page = number_of_blocks_per_page
        

        self._wb = Workbook()
        ws = self._wb.active

        for i in range(0,len(self.filled_data)):
            adata, datas = self.filled_data[i]

            self._exb_in_st.Id = i + 1
            self._exb_in_st.fill_blank(self.oto_loc, adata, self.mtm_loc, datas, receipt=self._receipt)
            self._exb_in_st.put_it_in_sheet(ws)

    def page_setup(self,
        margins=NORMAL, 
        scale=100, 
        paperSize='0', 
        orientation='portrait', 
        horizontalcentered=True, 
        verticalcentered=True
    ):
        self._exb_in_st.pagelayout(self._wb.active, margins, scale, paperSize, orientation, horizontalcentered, verticalcentered)

    def output(self, out_file):
        try:
            self._wb.save(out_file)
            
        except Exception as e:
            print(f"There was an error: {repr(e)}")
        else:
            print("There was no error!")
        finally:
            print(f'输出成功，请打开 {out_file} 查看输出结果')       

    def to_bathpay(self, out_file, topamount:float=900000):
        def split(rec:Series, topamount):
            data = []
            count = int(ceil( rec.转款金额 / topamount))
            for i in range(1,count):
                s = rec.copy()
                s.转款金额 = topamount
                data.append(s)
            s = rec.copy()
            s.转款金额 = s.转款金额 - topamount*(count-1)
            data.append(s)
            return DataFrame(data)

        if self.tidy_data.shape[0] == 0:
            print('没有符合条件的记录需要转换。')
        else:
            col =[
                '交易金额（，黄色部分为必填）',
                '付款账号',
                '付款账号名称',
                '付款账户开户行',
                '收款账户开户行名称',
                '收款账号',
                '收款账户名称',
                '汇款用途',
                '是否交行账号（0-交行，1-他行）',
                '是否同城（1-异地，0-同城）',
                '收款方支付行号（建议输入联行号，减少落地处理失败率。）',
                '币种（统一为CNY）',
                'ERP序号',
                '是否预约（0-不预约，1-预约,不填默认0）',
                '预约时间(20991212090000)',
                '附言']

            df = self.tidy_data.copy()
            df.转款金额 = df.转款金额.astype(float)

            result = DataFrame([])
            
            for _,row in df.iterrows():
                if row.转款金额>topamount:
                    result = concat([result,split(row,topamount)]) 
                else:
                    result = concat([result,row.to_frame().T])
            result = result.reset_index(drop=True)

            result['是否交行'] = result.开户银行.apply(lambda x: 0 if ('交行' in x)|('交通银行' in x) else 1)
            result['是否同城'] = 1
            result.银行账号 = result.银行账号.astype(str)

            bathpay = DataFrame([], columns=col)
            for _,row in result.iterrows():
                s = Series(
                    {
                        '交易金额（，黄色部分为必填）': row.转款金额,
                        '付款账号': '361899991011000027850',
                        '付款账号名称': '江西庆合再生资源有限公司',
                        '付款账户开户行': '交通银行南昌赣江新区支行',
                        '收款账户开户行名称': row.开户银行,
                        '收款账号': row.银行账号,
                        '收款账户名称': row.收款单位,
                        '汇款用途': row.用途,
                        '是否交行账号（0-交行，1-他行）': row.是否交行,
                        '是否同城（1-异地，0-同城）': row.是否同城,
                        '收款方支付行号（建议输入联行号，减少落地处理失败率。）':row.行号,
                        '币种（统一为CNY）': 'CNY',  
                        'ERP序号': None,
                        '是否预约（0-不预约，1-预约,不填默认0）': 0,
                        '预约时间(20991212090000)' : None,
                        '附言': None    
                    }
                )
                bathpay = concat([bathpay, DataFrame(s).T])        

        

            try:
                bathpay.to_excel(out_file,index=False)
                
            except Exception as e:
                print(f"There was an error: {repr(e)}")
            else:
                print("There was no error!")
            finally:
                print(f'输出成功，请打开 {out_file} 查看输出结果')       